########################################################################
#  Pyfm - personal finance data-base
#  Copyright (C) 2007,8 Ehud Ben-Reuven
#  udi@benreuven.com
#
# This program is free software; you can redistribute it and/or
# modify it under the terms of the GNU General Public License
# as published by the Free Software Foundation version 2.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
########################################################################
"""Convert BLL IL Portfolio Position XLS file to flat lines.
"""
from csv2flt import *
from obj.flat import stagvalue,ptagvalue,pacctid,psecid
import re
from xls2csv2 import xls2csv2
from obj import dt

def acctidre(name):
	return '(?P<%s>[\d\-\/]+)'%name

def secid(name):
	return '(?P<%s>\d{6,7})'%name

htf_list=[
#version 0
([
	['', '', '', '', '', '', '', '', '', '', ''],
	['\xee\xe1\xe8 \xfa\xe9\xf7', '', '', '', '', '', '', '', '', '', ''], # In Hebrew: Portfolio view
	['('+'\xfa\xe0\xf8\xe9\xea: '+eurdre('DTASOF','/')+')?', '', '', '', '', '', '', '', '', '('+'\xfa\xe0\xf8\xe9\xea: '+eurdre('DTASOF','/')+')?', ''],
	['', '', '', '', '', '', '', '', '', '', ''],
	['', '', '', '', '', '', '', '', '', '', ''],
	['\xee\xf1\xf4\xf8 \xf0\xe9\xe9\xf8',
	 '\xf9\xed \xf0\xe9\xe9\xf8', 
	 '\xe0\xf8\xe5\xf2 \xeb\xf1\xf4\xe9', 
	 '\xf9\xf2\xf8 \xf7\xf0\xe9\xe4 \xee\xee\xe5\xf6\xf2', 
	 '\xeb\xee\xe5\xfa \xe0\xe7\xe6\xf7\xe4', 
	 '\xf9\xe5\xe5\xe9 \xe0\xe7\xe6\xf7\xe4', 
	 '\xf9\xf2\xf8', 
	 '\xe0\xe7\xe5\xe6 \xf9\xe9\xf0\xe5\xe9 \xe9\xe5\xee\xe9', 
	 '\xf8\xe5\xe5\xe7 \xe1-%', 
	 '\xf8\xe5\xe5\xe7 \xe1\xf9\xe7', 
	 '\xf9\xed \xfa\xe9\xf7']
],
[secid('SECID')+'.0',
      strre('SECNAME'),
      '(0\.0)|(\xec\xe0 \xf7\xe9\xe9\xed)',
      pvaluere('AVG_COST',1),
      pvaluere('UNITS',1),
      pvaluere('MKTVAL',1),
      pvaluere('UNITPRICE',1), # Surprisngly this can be in different currencies for different assets and is for 100 Units for bonds
      floatre('DAY_CHANGE_PERCENT'),
      floatre('CHANGE_PERCENT'),
      valuere('CGAIN_ILS',1),
      acctidre('ACCTID')],
None),
#version1
([
	['', '', '', '', '', '', '', '', '', '', ''],
	['\xee\xe1\xe8 \xe0\xe9\xf9\xe9 - \xe4\xe0\xe7\xe6\xf7\xe5\xfa \xf9\xec\xe9', '', '', '', '', '', '', '', '', '', ''], # In Hebrew: Portfolio view
	['\xfa\xe0\xf8\xe9\xea: '+eurdre('DTASOF','/'), '',':\xfa\xe9\xf7', acctidre('ACCTID'), '', '', '', '', '', '', ''],
	['', '', '', '', '', '', '', '', '', '', ''],
	['', '', '', '', '', '', '', '', '', '', ''],
	['\xee\xf1\xf4\xf8 \xf0\xe9\xe9\xf8',
	 '\xf9\xed \xf0\xe9\xe9\xf8',
	 '\xe0\xf8\xe5\xf2 \xeb\xf1\xf4\xe9',
	 '\xf9\xf2\xf8 \xf7\xf0\xe9\xe4 \xee\xee\xe5\xf6\xf2',
	 '\xeb\xee\xe5\xfa \xe0\xe7\xe6\xf7\xe4',
	 '\xf9\xe5\xe5\xe9 \xe0\xe7\xe6\xf7\xe4',
	 '% \xee\xe4\xfa\xe9\xf7', '\xf9\xf2\xf8',
	 '% \xf9\xe9\xf0\xe5\xe9 \xe9\xe5\xee\xe9',
	 '\xf8\xe5\xe5\xe7 \xe1-%',
	 "\xf8\xe5\xe5\xe7 \xe1\xf9''\xe7"]
],
[secid('SECID')+'.0',
      strre('SECNAME'),
      '(0\.0)|((\xec\xe0 )?\xf7\xe9\xe9\xed)',
      pvaluere('AVG_COST',1),
      pvaluere('UNITS',1),
      pvaluere('MKTVAL',1),
      pvaluere('PART_OF_PORTFOLIO',1),
      pvaluere('UNITPRICE',1), # Surprisngly this can be in different currencies for different assets and is for 100 Units for bonds
      floatre('DAY_CHANGE_PERCENT'),
      floatre('CHANGE_PERCENT'),
      valuere('CGAIN_ILS',1),
      ],
None),
#version2
([
	['', '', '', '', '', '', '', '', '', '', '', ''],
	['\xee\xe1\xe8 \xe0\xe9\xf9\xe9 - \xe4\xe0\xe7\xe6\xf7\xe5\xfa \xf9\xec\xe9', '', '', '', '', '', '', '', '', '', '', ''], # In Hebrew: Portfolio view
	['\xfa\xe0\xf8\xe9\xea: '+eurdre('DTASOF','/'), '',':\xfa\xe9\xf7', acctidre('ACCTID'), '', '', '', '', '', '', '', ''],
	['', '', '', '', '', '', '', '', '', '', '', ''],
	['', '', '', '', '', '', '', '', '', '', '', ''],
	['\xee\xf1\xf4\xf8 \xf0\xe9\xe9\xf8',
	 '\xf9\xed \xf0\xe9\xe9\xf8',
	 '\xe0\xf8\xe5\xf2 \xeb\xf1\xf4\xe9',
	 '\xf9\xf2\xf8 \xf7\xf0\xe9\xe4 \xee\xee\xe5\xf6\xf2',
	 '\xeb\xee\xe5\xfa \xe0\xe7\xe6\xf7\xe4',
	 '\xf9\xe5\xe5\xe9 \xe0\xe7\xe6\xf7\xe4',
	 '% \xee\xe4\xfa\xe9\xf7', '\xf9\xf2\xf8',
	 '% \xf9\xe9\xf0\xe5\xe9 \xe9\xe5\xee\xe9',
	 '\xf8\xe5\xe5\xe7 \xe1-%',
	 "\xf8\xe5\xe5\xe7 \xe1\xf9''\xe7", '\xee\xf1 \xe4\xfa\xe9\xf7']
],
[secid('SECID')+'.0',
      strre('SECNAME'),
      '(0\.0)|((\xec\xe0 )?\xf7\xe9\xe9\xed)',
      pvaluere('AVG_COST',1),
      pvaluere('UNITS',1),
      pvaluere('MKTVAL',1),
      pvaluere('PART_OF_PORTFOLIO',1),
      pvaluere('UNITPRICE',1), # Surprisngly this can be in different currencies for different assets and is for 100 Units for bonds
      floatre('DAY_CHANGE_PERCENT'),
      floatre('CHANGE_PERCENT'),
      valuere('CGAIN_ILS',1),
      acctidre('ACCTID'),
      ],
None),
]

def pre(fname,csvfile):
	xls2csv2(fname,csvfile,'hebrew')
	
def flat(csvfile,fout,linetxt):
	h,t,f,version=readcsvtable_multiformat(csvfile,htf_list)
	if h==None:
		raise Exception("File does not match template")

	l=h[2]
	dtasof = str(dt.expandyear(int(l['DTASOF_year'])))+l['DTASOF_month']+l['DTASOF_day']
	accttype='INVESTMENT'
	if version==1 or version==2:
		acctid=stagvalue('ACCTID',h[2]['ACCTID'])

	for l in t:
		units=float(l['UNITS'])
		unitprice=float(l['UNITPRICE'])/100
		mktval=float(l['MKTVAL'])
		if abs(mktval-units*unitprice)>0.5:
			print mktval,units*unitprice
		
		print >>fout,stagvalue('DTASOF',dtasof),
		print >>fout,stagvalue('SECID','TASE='+l['SECID']),stagvalue('SECNAME',l['SECNAME']),
		if version==0 or version==2:
			acctid=stagvalue('ACCTID',l['ACCTID'])
		print >>fout,acctid,stagvalue('ACCTTYPE','INVESTMENT'),
		print >>fout,stagvalue('UNITS',units),stagvalue('UNITPRICE',unitprice),stagvalue('MKTVAL',mktval),
		print >>fout,linetxt

help="""Convert BLL portfolio position XLS file to flat lines.
The XLS files should be located at $PYFM_DATA/imports/BLL_IL/portfolio/position/
the file is first converted to CSV file in $PYFM_DATA/temp
and then to flat file at  $PYFM_DATA/flt/BLL_IL/portfolio/position/
"""
def path(line):
	return ['BLL_IL','portfolio','position']

ext='*.xls'
